package com.web22_2_1.service.impl;

import com.web22_2_1.entity.vo.GradeVO;
import com.web22_2_1.mapper.CourseSQL;
import com.web22_2_1.mapper.GradeSQL;
import com.web22_2_1.entity.Grade;
import com.web22_2_1.service.CourseService;
import com.web22_2_1.service.GradeService;
import com.web22_2_1.service.StudentService;
import com.web22_2_1.utils.DruidDemo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GradeServiceImpl implements GradeService {
    // 课程服务接口，用于处理与课程相关的业务逻辑
    private CourseService courseService;
    // 学生服务接口，用于处理与学生相关的业务逻辑
    private StudentService studentService;

    // 构造方法，初始化CourseService和StudentService
    public GradeServiceImpl() {
        // TODO Auto-generated constructor stub
        // 初始化学生服务和课程服务实例
        studentService = new StudentServiceImpl();
        courseService = new CourseServiceImpl();
    }

    // 查询所有成绩信息
    @Override
    public List<GradeVO> listAll() {
        // TODO Auto-generated method stub
        Connection connection = null;
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        List<GradeVO> list = new ArrayList<>();
        try {
            // 获取数据库连接
            connection = DruidDemo.getConnection();
            // 准备SQL语句
            pStatement = connection.prepareStatement(GradeSQL.SELECT_ALL_GRADE);
            // 执行查询
            resultSet = pStatement.executeQuery();
            // 遍历查询结果，转换为GradeVO对象并添加到列表中
            while(resultSet.next()) {
                GradeVO gradeVO = new GradeVO();
                gradeVO.setGradeId(resultSet.getInt("gradeId"));
                int studentId = resultSet.getInt("studentId");
                gradeVO.setStudentName(studentService.getStudentById(studentId).getName());
                int courseId = resultSet.getInt("courseId");
                gradeVO.setCourseName(courseService.getCourseById(courseId).getCourseName());
                gradeVO.setScore(resultSet.getFloat("score"));
                gradeVO.setTerm(resultSet.getString("term"));
                gradeVO.setExamDate(resultSet.getDate("examDate"));
                list.add(gradeVO);
            }
        } catch (SQLException e) {
            // TODO: handle exception
            // 打印异常信息
            e.printStackTrace();
        }  finally {
            // 关闭数据库连接和资源
            DruidDemo.destroy(connection, pStatement, resultSet);
        }
        return list;
    }

    // 根据成绩ID查询成绩信息
    @Override
    public GradeVO getGradeById(int gradeId) {
        GradeVO grade = null;
        Connection connection = null;
        PreparedStatement pStatement = null;
        try {
            // 获取数据库连接
            connection = DruidDemo.getConnection();
            // 准备SQL语句
            pStatement = connection.prepareStatement(GradeSQL.SELECT_GRADE_BY_ID);
            pStatement.setInt(1, gradeId);
            // 执行查询
            ResultSet rs = pStatement.executeQuery();
            // 遍历查询结果，转换为GradeVO对象
            while (rs.next()) {
                grade = new GradeVO();
                grade.setGradeId(rs.getInt("GradeID"));
                grade.setStudentName(studentService.getStudentById(rs.getInt("StudentID")).getName());
                grade.setCourseName(courseService.getCourseById(rs.getInt("CourseID")).getCourseName());
                grade.setScore(rs.getFloat("Score"));
                grade.setTerm(rs.getString("Term"));
                grade.setExamDate(rs.getDate("ExamDate"));
            }
        } catch (SQLException e) {
            // TODO: handle exception
            // 打印异常信息
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和资源
            DruidDemo.destroy(connection, pStatement, null);
        }
        return grade;
    }

    // 添加成绩信息
    @Override
    public boolean addGrade(GradeVO grade) {
        Connection connection = null;
        PreparedStatement pStatement = null;
        int rows = 0;
        try {
            // 获取数据库连接
            connection = DruidDemo.getConnection();
            // 准备SQL语句
            pStatement = connection.prepareStatement(GradeSQL.ADD_GRADE);
            // 设置SQL参数
            pStatement.setInt(1,studentService.getStudentByName(grade.getStudentName()).getStudentId());
            pStatement.setInt(2,courseService.getCourseByName(grade.getCourseName()).getCourseId());
            pStatement.setFloat(3,grade.getScore());
            pStatement.setString(4,grade.getTerm());
            pStatement.setDate(5,new java.sql.Date(grade.getExamDate().getTime()));
            // 执行更新
            rows = pStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO: handle exception
            // 打印异常信息
            e.printStackTrace();
        }
        finally {
            // 关闭数据库连接和资源
            DruidDemo.destroy(connection, pStatement, null);
        }
        // 返回更新结果
        return rows>0;
    }

    // 更新成绩信息
    @Override
    public boolean updateGrade(GradeVO grade) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;
        try{
            // 获取数据库连接
            conn = DruidDemo.getConnection();
            // 准备SQL语句
            pstmt = conn.prepareStatement(GradeSQL.UPDATE_GRADE);
            // 设置SQL参数
            pstmt.setInt(1,studentService.getStudentByName(grade.getStudentName()).getStudentId());
            pstmt.setInt(2,courseService.getCourseByName(grade.getCourseName()).getCourseId());
            pstmt.setFloat(3,grade.getScore());
            pstmt.setString(4,grade.getTerm());
            pstmt.setDate(5,new java.sql.Date(grade.getExamDate().getTime()));
            pstmt.setInt(6,grade.getGradeId());
            // 执行更新
            rows = pstmt.executeUpdate();
        } catch (SQLException e){
            // 打印异常信息
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和资源
            DruidDemo.destroy(conn,pstmt,null);
        }
        // 返回更新结果
        return rows>0;
    }

    // 删除成绩信息
    @Override
    public boolean deleteGrade(int gradeId) {
        Connection connection = null;
        PreparedStatement pStatement = null;
        int rows = 0;
        try {
            // 获取数据库连接
            connection = DruidDemo.getConnection();
            // 准备SQL语句
            pStatement = connection.prepareStatement(GradeSQL.DELETE_GRADE_BY_ID);
            pStatement.setInt(1,gradeId);
            // 执行更新
            rows = pStatement.executeUpdate();
        } catch (SQLException e) {
            // 打印异常信息
            e.printStackTrace();
        }
        // 返回更新结果
        return rows>0;
    }
}
